import numpy as np
import pandas as pd
from common_func_defs import *
from datetime import datetime, timedelta, date
from fuzzywuzzy import process
from initdatabase import *
from interactdb import *

def fuzzy_filter_dataframe(df, column_name, target_string, threshold=70):
    """
    在DataFrame中筛选出匹配相似度大于阈值的行

    Parameters:
        df (pd.DataFrame): 要处理的DataFrame
        column_name (str): 要匹配的列名
        target_string (str): 目标字符串
        threshold (int): 相似度阈值，默认为70

    Returns:
        pd.DataFrame: 符合条件的行组成的DataFrame
    """
    # 使用process库来模糊匹配查询
    matches = process.extract(target_string, df[column_name], limit=None)

    # 筛选符合相似度阈值的结果
    filtered_matches = [(match[0], match[1]) for match in matches if match[1] >= threshold]

    # 创建包含匹配结果的新DataFrame
    result_df = df[df[column_name].isin([match[0] for match in filtered_matches])]

    return result_df

####################################################### 加工方法定义 ########################################################
'''—————————————————————————————方法：抖音旗舰店直播间日统计表（达播报表加工）—————————————————————————————'''
def dy_flagship_store_living_room_day_stat_calcu(df_dy_flagship_store_living_room_day, whether_group_by_stat_time):

    if whether_group_by_stat_time:
        group_list = ['stat_time', 'livestreamer_douyin_id']
    else:
        group_list = ['livestreamer_douyin_id']

    engine = create_engine(DB_CONNECT)
    df_69_info = pd.read_sql_query('SELECT * FROM good_indicator', engine)
    df_69_info.drop(['id', 'create_time'], axis=1, inplace=True)
    tp = df_dy_flagship_store_living_room_day.merge(df_69_info, on='sixty_nine_code', how='left')

    # sixty_nine_code
    output = tp.groupby(group_list)['sixty_nine_code'].agg(lambda x: ';'.join(x.unique())).reset_index(
        name='sixty_nine_code')

    # livestreamer_name
    df_ttp = tp.groupby(group_list)['livestreamer_name'].agg(lambda x: ';'.join(x.unique())).reset_index(
        name='livestreamer_name')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_duration
    df_ttp = tp.groupby(group_list)['living_duration'].agg(lambda x: int(x.mean())).reset_index(name='living_duration')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_times
    df_ttp = tp.groupby(group_list).size().reset_index(name='living_times')
    output = output.merge(df_ttp, on=group_list, how='left')
    output['living_times'] = output['living_times'].apply(lambda x: int(x))
    del df_ttp

    # living_max_online_user_count
    df_ttp = tp.groupby(group_list)['living_max_online_user_count'].agg(lambda x: int(x.max())).reset_index(name='living_max_online_user_count')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_average_online_user_count
    df_ttp = tp.groupby(group_list)['living_average_online_user_count'].agg(lambda x: x.mean()).\
        reset_index(name='living_average_online_user_count')
    output = output.merge(df_ttp, on=group_list, how='left')
    output['living_average_online_user_count'] = output['living_average_online_user_count'].apply(lambda x: int(x))
    del df_ttp

    # living_all_goods_view_to_trans_count_rate
    df_ttp = tp.groupby(group_list).apply(
        lambda x: x['living_all_goods_trans_count'].sum()/ x['living_view_count'].sum()).reset_index(name='living_all_goods_view_to_trans_count_rate')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_trans_amount
    df_ttp = tp.groupby(group_list)['living_all_goods_trans_amount'].agg(lambda x: x.mean()).\
        reset_index(name='living_trans_amount')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_trans_count
    df_ttp = tp.groupby(group_list).apply(lambda x: x['living_all_goods_trans_good_count'].mean()).\
        reset_index(name='living_trans_count')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_refund_amount
    df_ttp = tp.groupby(group_list).apply(lambda x: x['living_all_goods_refund_amount'].mean()).\
        reset_index(name='living_refund_amount')
    output = output.merge(df_ttp, on=group_list, how='left')
    del df_ttp

    # living_actual_trans_amount
    output['living_actual_trans_amount'] = output['living_trans_amount'] - output['living_refund_amount']

    #################################################### 乱计算均值 ######################################################

    tp = tp.dropna()  # tp
    if tp.empty:
        # output['cal_livings_count'] = np.nan

        # # living_initial_trans_amount
        # output['living_initial_trans_amount'] = np.nan
        #
        # # living_service_fee
        # output['living_service_fee'] = np.nan
        #
        # # living_commission
        # output['living_commission'] = np.nan
        #
        # # living_discount_rate
        # output['living_discount_rate'] = np.nan
        #
        # # living_giveaway_item_price_ratio
        # output['living_giveaway_item_price_ratio'] = np.nan
        #
        # # living_fee_rate
        # output['living_fee_rate'] = np.nan
        #
        # # living_roi
        # output['living_roi'] = np.nan
        pass
    else:
        # cal_livings_count
        df_ttp = tp.groupby(group_list).size().reset_index(name='cal_livings_count')
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp

        # living_initial_trans_amount
        df_ttp = tp.groupby(group_list).apply(
            lambda x: (x['good_initial_price'] * x['living_all_goods_trans_count']).mean()). \
            reset_index(name='living_initial_trans_amount')
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp

        # living_service_fee
        df_ttp = tp.groupby(group_list).apply(lambda x: x['living_service_fee'].mean()). \
            reset_index(name='living_service_fee')
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp

        # living_commission
        df_ttp = tp.groupby(group_list).apply(lambda x: x['living_commission'].mean()). \
            reset_index(name='living_commission')
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp

        # living_discount_rate
        tp['living_discount_rate'] = (tp['good_initial_price']-(tp['living_all_goods_trans_amount']/tp['living_all_goods_trans_count'])) / tp['good_initial_price']
        # 先计算单个直播的折扣率
        df_ttp = tp.groupby(group_list).apply(lambda x: x['living_discount_rate'].mean()). \
            reset_index(name='living_discount_rate')
        # 直接取得平均
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp


        # living_giveaway_item_price_ratio
        # 先计算单个直播的折扣率
        df_ttp = tp.groupby(group_list).apply(lambda x: x['good_giveaway_item_price_ratio'].mean()). \
            reset_index(name='living_giveaway_item_price_ratio')
        # 直接取得平均
        output = output.merge(df_ttp, on=group_list, how='left')
        del df_ttp

        # living_fee_rate
        tp['living_fee_rate'] = (tp['living_service_fee'] + tp['living_commission']) / (tp['good_initial_price'] * tp['living_all_goods_trans_count']) + tp['living_discount_rate'] + tp['good_giveaway_item_price_ratio']
        # 先计算单个living_fee_rate率
        df_ttp = tp.groupby(group_list).apply(lambda x: x['living_fee_rate'].mean()). \
            reset_index(name='living_fee_rate')
        # 直接取得平均
        output = output.merge(df_ttp, on=group_list, how='left')

        # living_roi
        output['living_roi'] = 1 / output['living_discount_rate']
    #################################################### 乱计算均值 ######################################################

    #################################################### 加权计算均值 #####################################################
    # # living_initial_trans_amount
    # df_ttp = tp.groupby(group_list).apply(
    #     lambda x: (x['good_initial_price'] * x['living_all_goods_trans_count']).mean()). \
    #     reset_index(name='living_initial_trans_amount')
    # output = output.merge(df_ttp, on=group_list, how='left')
    # del df_ttp
    #
    # # living_service_fee
    # df_ttp = tp.groupby(group_list).apply(lambda x: x['living_service_fee'].mean()). \
    #     reset_index(name='living_service_fee')
    # output = output.merge(df_ttp, on=group_list, how='left')
    # del df_ttp
    #
    # # living_commission
    # df_ttp = tp.groupby(group_list).apply(lambda x: x['living_commission'].mean()). \
    #     reset_index(name='living_commission')
    # output = output.merge(df_ttp, on=group_list, how='left')
    # del df_ttp
    #
    # # living_discount_rate
    # output['living_discount_rate'] = (output['living_initial_trans_amount'] - output['living_actual_trans_amount']) / \
    #                                  output['living_initial_trans_amount']
    #
    # # living_giveaway_item_price_ratio
    # df_ttp = tp.groupby(group_list).apply(lambda x:
    #                                       (x['good_giveaway_item_price_ratio'] * x['good_initial_price'] * x[
    #                                           'living_all_goods_trans_count']).sum() / (x['good_initial_price'] * x[
    #                                           'living_all_goods_trans_count']).sum()). \
    #     reset_index(name='living_giveaway_item_price_ratio')
    # output = output.merge(df_ttp, on=group_list, how='left')
    #
    # del df_ttp
    #
    # # living_fee_rate
    # output['living_fee_rate'] = (output['living_service_fee'] + output['living_commission']) / output[
    #     'living_initial_trans_amount'] + output['living_discount_rate'] + output['living_giveaway_item_price_ratio']
    #
    # # living_roi
    # output['living_roi'] = 1 / output['living_discount_rate']
    #
    # # output = output.applymap(format_number)
    ####################################################加权计算均值######################################################

    return output


def dy_flagship_store_living_room_day_stat_exe(df_dy_flagship_store_living_room_day):  # 上传表格时同时生成day_stat（前端不用）
    # 提供一个表格（不要为空），经过处理后上传到 dy_flagship_store_living_room_day_stat
    upload_df = dy_flagship_store_living_room_day_stat_calcu(df_dy_flagship_store_living_room_day, True)
    session = get_session()

    # 首先按照 stat_time 列进行分组
    grouped = upload_df.groupby('stat_time')

    # 遍历每个分组
    for stat_time, group_df in grouped:
        refresh_insert(session, group_df, dy_flagship_store_living_room_day_stat, stat_time)  # 逐日期刷新入库
    pass

def print_dy_flagship_store_living_room_day_stat(start_date, end_date, target_livestreamer_name=''):  # 展示day_stat（前端使用）
    # start_date = '2023-08-25'
    # end_date = '2023-08-30'
    # target_livestreamer_name = ''

    # 按照日期取数，取完了模糊筛选
    engine = create_engine(DB_CONNECT)
    df_dy_flagship_store_living_room_day_stat = pd.read_sql_query(
        f"SELECT * FROM dy_flagship_store_living_room_day_stat WHERE stat_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)

    if df_dy_flagship_store_living_room_day_stat.empty:
        return None  # 如果为空返回
    else:
        if target_livestreamer_name != '':  # 如果有筛选需求，则筛选
            df_dy_flagship_store_living_room_day_stat = \
                fuzzy_filter_dataframe(df_dy_flagship_store_living_room_day_stat, 'livestreamer_name', target_livestreamer_name)

        # 模糊筛选，如果是空则返回空
        if df_dy_flagship_store_living_room_day_stat.empty:
            return None
        else:
            df_dy_flagship_store_living_room_day_stat = uploaded_field_corr_entozh_res(df_dy_flagship_store_living_room_day_stat, '抖音旗舰店直播间日统计表')

            return df_dy_flagship_store_living_room_day_stat

def print_dy_flagship_store_living_room_day_predict(start_date: str, end_date: str, sixty_nine_code: str):  # 达播预测

    # start_date = '2023-08-25'
    # end_date = '2023-08-30'
    # sixty_nine_code = '6921443499044'

    engine = create_engine(DB_CONNECT)
    df_dy_flagship_store_living_room_day = pd.read_sql_query(
        f"SELECT * FROM dy_flagship_store_living_room_day WHERE stat_time BETWEEN '{start_date}' AND '{end_date}' AND sixty_nine_code = '{sixty_nine_code}'",
        engine)
    if df_dy_flagship_store_living_room_day.empty:
        return None
        # 查询结果为空

    df_dy_flagship_store_living_room_day.drop(['id', 'create_time', 'upload_time'], axis=1, inplace=True)

    output = dy_flagship_store_living_room_day_stat_calcu(df_dy_flagship_store_living_room_day, False)

    # 加工报表表字段英文名→中文名
    def predict_corr_entozh_res(upload_df, table_zh_name):
        base_name_df = pd.read_excel('C:/Users/简皓/Desktop/项目/丝宝/品相/预测输出数据字典_0401更新.xlsx', table_zh_name, header=1)  # 前端修改位置，表在腾讯文档

        # 创建从中文名到英文名的映射字典
        column_name_map = base_name_df.set_index('字段名')['释义'].to_dict()
        column_name_map["row_count"] = "数据记录数量"
        # 使用映射字典重命名upload_df的列名，中文名（释义）→英文名（字段名）
        for old_col, new_col in column_name_map.items():
            if old_col in upload_df.columns:
                upload_df.rename(columns={old_col: new_col}, inplace=True)
        return upload_df

    output = predict_corr_entozh_res(output, '抖音达播预测结果表')  # 改为中文名

    return output


'''—————————————————————————————方法：查价结果表1、2（查价加工）—————————————————————————————'''
def check_price_res1_exe(product_bar_code_and_product_num):
    engine = create_engine(DB_CONNECT)

    import re
    pattern = r'([a-zA-Z0-9]+):(\d+)(;[a-zA-Z0-9]+:\d+)*'

    if re.match(pattern=pattern, string=product_bar_code_and_product_num) is None:
        raise ValueError(
            "请输入正确的筛选条件，示例6921443441714:2;6921443441974:2")  # product_bar_code_and_product_num 字符串不合规范

    #     # 按分号分隔字符串
    substrings = product_bar_code_and_product_num.split(";")

    # 分隔每个子字符串并存储在两个列表中
    product_bar_code = [sub.split(":")[0] for sub in substrings]  # 可能是条码 也可能是 代码
    product_num = [int(sub.split(":")[1]) for sub in substrings]

    non_digit_count = len(re.findall(r'\D', product_bar_code[0]))

    if non_digit_count <= 1:
        whether_code = False
    else:
        whether_code = True

    if whether_code:
        combine_colname = ['product_code']
        pass
    else:
        combine_colname = ['product_bar_code']
        pass

    df_tp = pd.DataFrame(
        {
            combine_colname[0]: product_bar_code,
            'product_pack_count': product_num}
    )
    df_tp['sixty_nine_code'] = 69
    df_tp['product_combination'] = product_bar_code_and_product_num

    ######################## 处理开单价 ########################
    # 连接表
    shangpin_jiage = pd.read_sql_query('SELECT * FROM good_product_price', engine)
    shangpin_jiage.drop(['id', 'upload_time', 'create_time', 'product_code', 'product_name'], axis=1, inplace=True)
    shangpin_jiage['product_bar_code'] = shangpin_jiage['product_bar_code'].str.replace(' ', '')

    df_type = pd.read_sql_query('SELECT * FROM good_product_code', engine)
    df_type.drop(['id', 'upload_time', 'create_time'], axis=1, inplace=True)

    shangpin_jiage = pd.merge(df_type, shangpin_jiage, on='product_bar_code', how='left')
    shangpin_jiage.fillna(0, inplace=True)  # 把type表中存在，但是价格体系表中不存在的值定为0
    shangpin_jiage['product_series'].replace(0, '', inplace=True)

    df_tp = pd.merge(df_tp, shangpin_jiage, on=combine_colname, how='left')  # 此处建议还是让他们上传全的type表
    df_tp['product_bar_code'] = df_tp[combine_colname[0]]  # 更新bar code为当前输入的列名

    del shangpin_jiage, df_type

    ####################### 赠品处理 ########################

    zengpin_jiage = pd.read_sql_query('SELECT * FROM good_giveaway_item_price', engine)
    zengpin_jiage.drop(['id', 'upload_time', 'create_time'], axis=1, inplace=True)

    zengpin_jiage['giveaway'] = True

    df_tp = pd.merge(df_tp, zengpin_jiage, left_on='product_bar_code', right_on='giveaway_item_bar_code', how='left')
    del zengpin_jiage

    # 赠品 product_retail_price
    df_tp['good_giveaway_item_price'] = df_tp['product_pack_count'] * df_tp['giveaway_item_price_per_pack']
    df_giveaway_item_price = df_tp.groupby('sixty_nine_code').agg(
        good_giveaway_item_price=('good_giveaway_item_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_giveaway_item_price", axis=1)
    df_tp = pd.merge(df_tp, df_giveaway_item_price, on='sixty_nine_code', how='left')
    del df_giveaway_item_price

    # def check_na_rows(df):
    #     na_rows = df[df['product_initial_price'].isna() & df['giveaway_item_bar_code'].isna()]
    #     return [''.join(na_rows['product_bar_code']), len(na_rows)]
    #
    # na_bar_code = check_na_rows(df_tp)  # 第一个元素是缺失的bar code
    # if na_bar_code[1] == len(df_tp):
    #     return None  # 表明他们查询的 product_bar_code_and_product_num 内，按照条码全都是匹配不上的

    df_tp['giveaway'].fillna(False, inplace=True)
    try:
        # 选择giveaway取值为TRUE的行并保存到df_giveaway_item_combination中
        df_giveaway_item_combination = df_tp[df_tp['giveaway'] == True].copy()

        # 生成新变量giveaway_item_combination取值为变量pack_count(int)和type(str)的作为字符串粘贴后的值
        df_giveaway_item_combination['good_giveaway_item_combination'] = df_giveaway_item_combination['product_pack_count'].astype(str) + '*' + \
                                                                    df_giveaway_item_combination['product_code']
        df_giveaway_item_combination = df_giveaway_item_combination[['sixty_nine_code', 'good_giveaway_item_combination']]
        df_giveaway_item_combination = df_giveaway_item_combination.groupby('sixty_nine_code')['good_giveaway_item_combination'].agg(lambda x: '+'.join(x))
        df_tp = pd.merge(df_tp, df_giveaway_item_combination, on='sixty_nine_code', how='left')
        del df_giveaway_item_combination
    except:
        pass

    df_tp['product_total_pieces'] = df_tp['product_pack_pieces'] * df_tp['product_pack_count']
    df_tp['product_pack_giveaway_item_price'] = df_tp['giveaway_item_price_per_pack']
    df_tp['product_total_giveaway_item_price'] = df_tp['giveaway_item_price_per_pack'] * df_tp['product_pack_count']
    df_tp['product_total_initial_price'] = df_tp['product_initial_price'] * df_tp['product_pack_count']
    df_tp['product_total_retail_price'] = df_tp['product_retail_price'] * df_tp['product_pack_count']
    df_tp['product_total_small_promotion_price'] = df_tp['product_small_promotion_price'] * df_tp['product_pack_count']
    df_tp['product_total_big_promotion_price'] = df_tp['product_big_promotion_price'] * df_tp['product_pack_count']

    df_tp = df_tp[['product_bar_code',
                   'product_name',
                   'product_pack_count',
                   'product_pack_pieces',
                   'product_total_pieces',
                   'product_pack_giveaway_item_price',
                   'product_total_giveaway_item_price',
                   'product_initial_price',
                   'product_total_initial_price',
                   'product_retail_price',
                   'product_total_retail_price',
                   'product_small_promotion_price',
                   'product_total_small_promotion_price',
                   'product_big_promotion_price',
                   'product_total_big_promotion_price']]

    return df_tp

def check_price_res2_exe(good_actual_price, product_bar_code_and_product_num):
    try:
        good_actual_price = float(good_actual_price)
    except:
        return '请输入正确的到手价'

    engine = create_engine(DB_CONNECT)

    df_tp0 = check_price_res1_exe(product_bar_code_and_product_num=product_bar_code_and_product_num)
    if df_tp0 is None:
        return None

    good_pack_count = sum(df_tp0['product_pack_count'])
    good_total_pieces = sum(df_tp0['product_total_pieces'])
    good_giveaway_item_price = sum(df_tp0['product_total_giveaway_item_price'])
    good_initial_price = sum(df_tp0['product_total_initial_price'])
    good_small_promotion_price = sum(df_tp0['product_total_small_promotion_price'])
    good_big_promotion_price = sum(df_tp0['product_total_big_promotion_price'])
    def divide(a, b):
        if b == 0:
            return None
        return a / b
    good_each_piece_price = divide(good_actual_price, good_total_pieces)
    good_discount_fee_rate = divide((good_initial_price - good_actual_price), good_initial_price)
    good_giveaway_item_price_ratio = divide(good_giveaway_item_price, good_initial_price)
    good_is_break_big_promotion = '是' if good_actual_price < good_big_promotion_price else '否'
    good_is_break_small_promotion = '是' if good_actual_price < good_small_promotion_price else '否'

    df_tp = pd.DataFrame({
        'good_actual_price': [good_actual_price],
        'good_pack_count': [good_pack_count],
        'good_total_pieces': [good_total_pieces],
        'good_giveaway_item_price': [good_giveaway_item_price],
        'good_initial_price': [good_initial_price],
        'good_small_promotion_price': [good_small_promotion_price],
        'good_big_promotion_price': [good_big_promotion_price],
        'good_each_piece_price': [good_each_piece_price],
        'good_discount_fee_rate': [good_discount_fee_rate],
        'good_giveaway_item_price_ratio': [good_giveaway_item_price_ratio],
        'good_is_break_big_promotion': [good_is_break_big_promotion],
        'good_is_break_small_promotion': [good_is_break_small_promotion],
    })

    return df_tp


def check_price_exe(good_actual_price, product_bar_code_and_product_num):
    df1 = check_price_res1_exe(product_bar_code_and_product_num)
    if df1 is not None:
        #0131 删除 df1 = df1.fillna('nan')
        df1 = df1.applymap(format_number)
        # 转中文字段名
        df1 = uploaded_field_corr_entozh_res(df1, '查价结果表1')
    else:
        df1 = pd.DataFrame()

    df2 = check_price_res2_exe(good_actual_price, product_bar_code_and_product_num)
    if df2 is not None:
        #0131 删除 df2 = df2.fillna('nan')
        df2 = df2.applymap(format_number)
        # 转中文字段名
        df2 = uploaded_field_corr_entozh_res(df2, '查价结果表2')
    else:
        df2 = pd.DataFrame()
    return [df1, df2]

###################################################### 前端调用加工方法 ########################################################
# '''—————————————————————————————功能：达播报表-展示抖音旗舰店直播间日统计表—————————————————————————————'''
# res_df = print_dy_flagship_store_living_room_day_stat(start_date, end_date, target_livestreamer_name) #参数:begin_date: str, end_date: str, target_livestreamer_name: str
#
# '''—————————————————————————————功能：达播报表-预测达播放—————————————————————————————'''
# res_df = print_dy_flagship_store_living_room_day_predict(start_date, end_date, sixty_nine_code) # start_date: str, end_date: str, sixty_nine_code: str
#
# '''—————————————————————————————功能：淘客报表-生成淘宝客竞品推广日统计表—————————————————————————————'''
# res_df = dtk_competing_brand_promotion_day_stat_exe('2024-01-01', '2024-01-05') #参数:begin_date: str, end_date: str
#
# '''—————————————————————————————功能：查价-生成查价结果表1、2——————————————————————————————'''
# res_df=check_price_exe(10,"6921443441714:2;6921443441974:2;6921443440731:5;6921443440730:2") #参数good_actual_price,product_bar_code_and_product_num
# res_df1=res_df[0]
# res_df2=res_df[1]
